from sqlalchemy import Column, Integer, String, Text, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship 
from app.core.config import settings 

# 创建数据库引擎
engine_args = {}
if settings.DB_TYPE.lower() == "sqlite":
    engine_args["connect_args"] = {"check_same_thread": False}

engine = create_engine(
    settings.DATABASE_URL, 
    **engine_args,
    pool_size=20,  # 增加连接池大小
    max_overflow=30,  # 增加最大溢出连接数
    pool_timeout=60  # 增加连接超时时间
)

# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建基类
Base = declarative_base()

# 定义任务表
class TaskDB(Base):
    __tablename__ = "tasks"
    
    task_id = Column(String, primary_key=True, index=True)  # task_id
    client_id = Column(String, index=True)
    status = Column(String, default="pending")
    total_dois = Column(Integer, default=0)
    success_dois = Column(Integer, default=0)
    completed_dois = Column(Integer, default=0)
    failed_dois = Column(Integer, default=0)
    start_time = Column(String)
    end_time = Column(String, default="")
    file_name = Column(String, default="")
    download_url = Column(String, default="")
    source_file_path = Column(String, default="")
    arq_job_id = Column(String, default="")
    task_type = Column(String, default="DOI")
    msg = Column(String, default="")
    # 关系：一个任务有多个DOI项
    dois = relationship("DOIItemDB", back_populates="task", cascade="all, delete-orphan")

# 定义DOI项表
class DOIItemDB(Base):
    __tablename__ = "doi_items"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    task_id = Column(String, ForeignKey("tasks.task_id", ondelete="CASCADE"), index=True)
    doi = Column(String, index=True)
    authors = Column(Text)  # 存储JSON字符串
    year = Column(String, default="")
    status = Column(String, default="pending")
    title = Column(String, default="")
    journal = Column(String, default="")
    abstract = Column(Text, default="")
    pdf_url = Column(String, default="")
    original_index = Column(Integer)
    msg = Column(String, default="")
    
    # 关系：多个DOI项属于一个任务
    task = relationship("TaskDB", back_populates="dois", cascade="save-update")


class NMPDDB(Base):
    __tablename__ = "nmpd"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    #task_id = Column(String, ForeignKey("tasks.task_id", ondelete="CASCADE"), index=True)
    code = Column(String, index=True) #注册证号，备案号
    content_json = Column(Text)  # 存储JSON字符串
    search_keyword = Column(String, default="")
    category = Column(Integer,default=0) # 0=境内医疗器械（备案） 1=境内医疗器械（备案历史数据） 
    url = Column(String, default="")
    nmpd_id=Column(String,default='') # 关联nmpd表id
 
class NMPDErrorDB(Base):
    __tablename__ = "nmpd_error"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    #task_id = Column(String, ForeignKey("tasks.task_id", ondelete="CASCADE"), index=True)
    code = Column(String,default="") #注册证号，备案号
    category = Column(Integer,default=0) # 0=境内医疗器械（备案） 1=境内医疗器械（备案历史数据） 
    search_keyword = Column(String, default="")
    error_type = Column(String, default="") # 分页错误，详情错误
    page = Column(String, default="") #页码
    msg = Column(String, default="")
    url = Column(String, default="")
    status=Column(Integer,default=0) # 0=未处理 1=已处理
    year_total=Column(Integer,default=0) # 总条数
    nmpd_id=Column(String,default='') # 关联nmpd表id
 
class ProjectDoingDB(Base):
    __tablename__ = "project_doing"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True) 
    project_name = Column(String, default="")
    class1 = Column(String, default="")
    class2 = Column(String, default="")   
    center_name = Column(String, default="")  
    subject = Column(String, default="")
    

 
 
 

# 创建数据库表
def create_tables():
    Base.metadata.create_all(bind=engine)

# 获取数据库会话
def get_db():
    db = SessionLocal()
    try:
        return db
    finally:
        db.close()